/*
 Navicat Premium Data Transfer

 Source Server         : localhost_5432
 Source Server Type    : PostgreSQL
 Source Server Version : 160003 (160003)
 Source Host           : localhost:5432
 Source Catalog        : familytree
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 160003 (160003)
 File Encoding         : 65001

 Date: 27/06/2024 02:57:40
*/


-- ----------------------------
-- Table structure for common
-- ----------------------------
DROP TABLE IF EXISTS "common";
CREATE TABLE "common" (
  "z_id" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "z_name" char(20) COLLATE "pg_catalog"."default",
  "fx_id" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "fx_name" char(20) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of common
-- ----------------------------
BEGIN;
INSERT INTO "common" ("z_id", "z_name", "fx_id", "fx_name") VALUES ('1                   ', '关系类型                ', '1                   ', '父亲                  '), ('1                   ', '关系类型                ', '2                   ', '养父                  '), ('1                   ', '关系类型                ', '3                   ', '养母                  '), ('1                   ', '关系类型                ', '4                   ', '母亲                  '), ('1                   ', '关系类型                ', '5                   ', '妻子                  '), ('1                   ', '关系类型                ', '7                   ', '未婚情人                '), ('1                   ', '关系类型                ', '8                   ', '女儿                  '), ('1                   ', '关系类型                ', '9                   ', '儿子                  '), ('1                   ', '关系类型                ', '10                  ', '私生儿子                '), ('1                   ', '关系类型                ', '11                  ', '私生女儿                '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('1                   ', '关系类型                ', '6                   ', '丈夫                  '), ('2                   ', '事件类别                ', '5                   ', '交通事故                '), ('2                   ', '事件类别                ', '6                   ', '升学                  '), ('1                   ', '关系类型                ', '1                   ', '父亲                  '), ('1                   ', '关系类型                ', '2                   ', '养父                  '), ('1                   ', '关系类型                ', '3                   ', '养母                  '), ('1                   ', '关系类型                ', '4                   ', '母亲                  '), ('1                   ', '关系类型                ', '5                   ', '妻子                  '), ('1                   ', '关系类型                ', '7                   ', '未婚情人                '), ('1                   ', '关系类型                ', '8                   ', '女儿                  '), ('1                   ', '关系类型                ', '9                   ', '儿子                  '), ('1                   ', '关系类型                ', '10                  ', '私生儿子                '), ('1                   ', '关系类型                ', '11                  ', '私生女儿                '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('1                   ', '关系类型                ', '12                  ', '丈夫                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('2                   ', '事件类别                ', '1                   ', '结婚                  '), ('1                   ', '关系类型                ', '6                   ', '丈夫                  '), ('2                   ', '事件类别                ', '5                   ', '交通事故                '), ('2                   ', '事件类别                ', '6                   ', '升学                  ');
COMMIT;

-- ----------------------------
-- Table structure for event
-- ----------------------------
DROP TABLE IF EXISTS "event";
CREATE TABLE "event" (
  "family_id" char(20) COLLATE "pg_catalog"."default",
  "event_time" char(60) COLLATE "pg_catalog"."default",
  "event_type" char(60) COLLATE "pg_catalog"."default",
  "event_des" char(255) COLLATE "pg_catalog"."default",
  "event_fj" char(255) COLLATE "pg_catalog"."default",
  "is_del" bool
)
;

-- ----------------------------
-- Records of event
-- ----------------------------
BEGIN;
INSERT INTO "event" ("family_id", "event_time", "event_type", "event_des", "event_fj", "is_del") VALUES ('001                 ', '1927/12/01                                                  ', '结婚                                                          ', '离弃妻妾，与宋美龄于12月1日结婚，从此与宋子文、孔祥熙联姻。                                                                                                                                                                                                                                ', '暂无                                                                                                                                                                                                                                                             ', 'f'), ('001                 ', '1975/04/05                                                  ', '重病                                                          ', '因心脏病在台北士林寓所去世                                                                                                                                                                                                                                                  ', '暂时没有                                                                                                                                                                                                                                                           ', 'f'), ('666                 ', '2024/6/27                                                   ', '哈某课过了                                                       ', '盲猜因为老师心情好，盲猜课过了                                                                                                                                                                                                                                                ', '暂无                                                                                                                                                                                                                                                             ', 'f'), ('354                 ', '2024/8/3                                                    ', '结婚                                                          ', '3                                                                                                                                                                                                                                                              ', '243                                                                                                                                                                                                                                                            ', 'f');
COMMIT;

-- ----------------------------
-- Table structure for family
-- ----------------------------
DROP TABLE IF EXISTS "family";
CREATE TABLE "family" (
  "family_id" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "people_id" char(20) COLLATE "pg_catalog"."default",
  "family_name" char(20) COLLATE "pg_catalog"."default",
  "family_sex" char(20) COLLATE "pg_catalog"."default",
  "family_birth" char(20) COLLATE "pg_catalog"."default",
  "family_die" char(20) COLLATE "pg_catalog"."default",
  "is_del" bool
)
;

-- ----------------------------
-- Records of family
-- ----------------------------
BEGIN;
INSERT INTO "family" ("family_id", "people_id", "family_name", "family_sex", "family_birth", "family_die", "is_del") VALUES ('001                 ', '3                   ', '蒋介石                 ', '男                   ', '1887/10/31          ', '1975/04/05          ', 'f'), ('002                 ', '2                   ', '宋美龄                 ', '女                   ', '1897/3/？            ', '2003/10/24          ', 'f'), ('003                 ', '3                   ', '蒋经国                 ', '男                   ', '1910/04/27          ', '1988/01/13          ', 'f'), ('004                 ', '3                   ', '蒋纬国                 ', '男                   ', '1916/10/06          ', '1997/09/23          ', 'f'), ('005                 ', '3                   ', '蒋孝文                 ', '男                   ', '1935/12/14          ', '1989/04/14          ', 'f'), ('006                 ', '3                   ', '蒋孝章                 ', '男                   ', '1938/?/?            ', '?/?/?               ', 'f'), ('007                 ', '3                   ', '蒋孝武                 ', '男                   ', '1945/04/25          ', '1991/07/01          ', 'f'), ('008                 ', '3                   ', '蒋孝勇                 ', '男                   ', '1948/10/27          ', '1996/12/22          ', 'f'), ('009                 ', '3                   ', '蒋孝严                 ', '男                   ', '1942/03/01          ', '------              ', 'f'), ('010                 ', '3                   ', '蒋孝慈                 ', '男                   ', '1942/03/01          ', '1996/02/04          ', 'f'), ('011                 ', '3                   ', '蒋友兰                 ', '女                   ', '1961/?/?            ', '未知                  ', 'f');
COMMIT;

-- ----------------------------
-- Table structure for gener
-- ----------------------------
DROP TABLE IF EXISTS "gener";
CREATE TABLE "gener" (
  "gen_id" char(255) COLLATE "pg_catalog"."default",
  "gen_name" char(20) COLLATE "pg_catalog"."default",
  "gen_father" char(20) COLLATE "pg_catalog"."default",
  "gen_mother" char(20) COLLATE "pg_catalog"."default",
  "gen_no" char(20) COLLATE "pg_catalog"."default",
  "is_del" bool
)
;

-- ----------------------------
-- Records of gener
-- ----------------------------
BEGIN;
INSERT INTO "gener" ("gen_id", "gen_name", "gen_father", "gen_mother", "gen_no", "is_del") VALUES ('2                                                                                                                                                                                                                                                              ', '蒋经国                 ', '蒋介石                 ', '毛福梅                 ', '第二代                 ', 'f'), ('4                                                                                                                                                                                                                                                              ', '蒋孝文                 ', '蒋经国                 ', '蒋方良                 ', '第三代                 ', 'f'), ('5                                                                                                                                                                                                                                                              ', '蒋孝章                 ', '蒋经国                 ', '蒋方良                 ', '第三代                 ', 'f'), ('6                                                                                                                                                                                                                                                              ', '蒋孝武                 ', '蒋经国                 ', '蒋方良                 ', '第三代                 ', 'f'), ('8                                                                                                                                                                                                                                                              ', '蒋孝慈                 ', '蒋经国                 ', '章亚若                 ', '第三代                 ', 'f'), ('9                                                                                                                                                                                                                                                              ', '蒋孝严                 ', '蒋经国                 ', '章亚若                 ', '第三代                 ', 'f'), ('11                                                                                                                                                                                                                                                             ', '蒋友梅                 ', '蒋孝文                 ', '徐乃锦                 ', '第四代                 ', 'f'), ('12                                                                                                                                                                                                                                                             ', '俞祖声                 ', '俞扬和                 ', '蒋孝章                 ', '第四代                 ', 'f'), ('13                                                                                                                                                                                                                                                             ', '蒋友松                 ', '蒋孝武                 ', '汪长诗                 ', '第四代                 ', 'f'), ('14                                                                                                                                                                                                                                                             ', '蒋友兰                 ', '蒋孝武                 ', '汪长诗                 ', '第四代                 ', 'f'), ('10                                                                                                                                                                                                                                                             ', '蒋孝刚                 ', '蒋纬国                 ', '邱爱伦                 ', '第三代                 ', 'f'), ('7                                                                                                                                                                                                                                                              ', '蒋蒋孝                 ', '蒋经国                 ', '蒋方良                 ', '第三代                 ', 'f');
COMMIT;

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS "menu";
CREATE TABLE "menu" (
  "menu_id" int4 NOT NULL,
  "menu_address" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "menu_level" int4 NOT NULL,
  "menu_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL
)
;

-- ----------------------------
-- Records of menu
-- ----------------------------
BEGIN;
INSERT INTO "menu" ("menu_id", "menu_address", "menu_level", "menu_name") VALUES (9, '/relation1', 1, '查询人员关系信息'), (10, '/event1', 1, '查询人员事件信息'), (1, '/family', 2, '个人信息'), (2, '/people', 2, '家族信息'), (3, '/event', 2, '人员事件信息'), (4, '/relation', 2, '人员关系信息'), (5, '/gener', 2, '家庭成员信息'), (11, '/tongjir', 2, '同代人统计'), (12, '/common', 2, '共通表'), (13, '/tree', 2, '家庭成员展示'), (6, '/people1', 1, '查询家族'), (7, '/gener1', 1, '查询家庭成员信息');
COMMIT;

-- ----------------------------
-- Table structure for people
-- ----------------------------
DROP TABLE IF EXISTS "people";
CREATE TABLE "people" (
  "people_id" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "user_id" char(20) COLLATE "pg_catalog"."default",
  "people_name" char(20) COLLATE "pg_catalog"."default",
  "description" char(20) COLLATE "pg_catalog"."default",
  "create_time" char(255) COLLATE "pg_catalog"."default",
  "is_del" bool
)
;

-- ----------------------------
-- Records of people
-- ----------------------------
BEGIN;
INSERT INTO "people" ("people_id", "user_id", "people_name", "description", "create_time", "is_del") VALUES ('1                   ', 'xwang               ', '蒋氏家族                ', '因课设被创建              ', '2022/6/15                                                                                                                                                                                                                                                      ', 'f'), ('3                   ', 'ck567               ', '曲氏家族                ', '我随便测试的              ', '2022/06/15                                                                                                                                                                                                                                                     ', 'f'), ('2                   ', 'xsong               ', '宋氏家族                ', '课设                  ', '2022/6/15                                                                                                                                                                                                                                                      ', 'f');
COMMIT;

-- ----------------------------
-- Table structure for relation
-- ----------------------------
DROP TABLE IF EXISTS "relation";
CREATE TABLE "relation" (
  "family_id1" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "id1_name" char(255) COLLATE "pg_catalog"."default",
  "family_id2" char(20) COLLATE "pg_catalog"."default" NOT NULL,
  "id2_name" char(255) COLLATE "pg_catalog"."default",
  "relation_type" char(255) COLLATE "pg_catalog"."default",
  "is_del" bool
)
;

-- ----------------------------
-- Records of relation
-- ----------------------------
BEGIN;
INSERT INTO "relation" ("family_id1", "id1_name", "family_id2", "id2_name", "relation_type", "is_del") VALUES ('006                 ', '蒋经国                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '儿子                                                                                                                                                                                                                                                             ', 'f'), ('007                 ', '蒋纬国                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '儿子                                                                                                                                                                                                                                                             ', 'f'), ('008                 ', '蒋瑶光                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '养女                                                                                                                                                                                                                                                             ', 'f'), ('010                 ', '蒋孝文                                                                                                                                                                                                                                                            ', '006                 ', '蒋经国                                                                                                                                                                                                                                                            ', '女儿                                                                                                                                                                                                                                                             ', 'f'), ('002                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '妻子                                                                                                                                                                                                                                                             ', 'f'), ('010                 ', '蒋孝文                                                                                                                                                                                                                                                            ', '006                 ', '蒋经国                                                                                                                                                                                                                                                            ', '女儿                                                                                                                                                                                                                                                             ', 'f'), ('010                 ', '蒋孝文                                                                                                                                                                                                                                                            ', '006                 ', '蒋经国                                                                                                                                                                                                                                                            ', '女儿                                                                                                                                                                                                                                                             ', 'f'), ('000                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '妻子                                                                                                                                                                                                                                                             ', 'f'), ('010                 ', '蒋孝文                                                                                                                                                                                                                                                            ', '006                 ', '蒋经国                                                                                                                                                                                                                                                            ', '女儿                                                                                                                                                                                                                                                             ', 'f'), ('002                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '前妻                                                                                                                                                                                                                                                             ', 'f'), ('002                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '前妻                                                                                                                                                                                                                                                             ', 'f'), ('002                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '前妻                                                                                                                                                                                                                                                             ', 'f'), ('002                 ', '宋美龄                                                                                                                                                                                                                                                            ', '001                 ', '蒋介石                                                                                                                                                                                                                                                            ', '妻子                                                                                                                                                                                                                                                             ', 'f');
COMMIT;

-- ----------------------------
-- Table structure for staff
-- ----------------------------
DROP TABLE IF EXISTS "staff";
CREATE TABLE "staff" (
  "staff_id" int4 NOT NULL,
  "staff_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "staff_password" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "staff_level" int4 NOT NULL,
  "staff_remarks" varchar(255) COLLATE "pg_catalog"."default",
  "is_del" bool NOT NULL DEFAULT false,
  "update_time" timestamptz(0) NOT NULL
)
;

-- ----------------------------
-- Records of staff
-- ----------------------------
BEGIN;
INSERT INTO "staff" ("staff_id", "staff_name", "staff_password", "staff_level", "staff_remarks", "is_del", "update_time") VALUES (20210702, 'testtest', 'testtest', 1, '普通用户', 'f', '2021-06-30 11:06:24+08'), (20210656, 'lemon', 'lemon', 1, '普通用户', 'f', '2021-06-30 11:06:24+08'), (2, 'qjx1112', '12345678', 2, '管理员', 'f', '2022-05-24 11:04:59+08'), (2020082114, 'xwang', '123456', 1, '普通用户', 'f', '2021-06-30 11:06:24+08'), (12345, '12345', '12345', 1, '普通用户', 'f', '2022-06-13 11:06:24+08'), (1234567, '1234567', '1234567', 1, '普通用户', 'f', '2022-06-13 11:06:24+08'), (3, 'MFM123', 'MFM123', 1, '普通用户', 'f', '2022-06-14 11:06:24+08');
COMMIT;

-- ----------------------------
-- Table structure for tji
-- ----------------------------
DROP TABLE IF EXISTS "tji";
CREATE TABLE "tji" (
  "gen_no" char(20) COLLATE "pg_catalog"."default",
  "count" int4
)
;

-- ----------------------------
-- Records of tji
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Function structure for count_common
-- ----------------------------
DROP FUNCTION IF EXISTS "count_common"();
CREATE OR REPLACE FUNCTION "count_common"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

declare
counts integer;

begin
   select count(*) into counts from common;
    return counts;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for count_event
-- ----------------------------
DROP FUNCTION IF EXISTS "count_event"();
CREATE OR REPLACE FUNCTION "count_event"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

declare
counts integer;

begin
   select count(*) into counts from event;
    return counts;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for count_family
-- ----------------------------
DROP FUNCTION IF EXISTS "count_family"();
CREATE OR REPLACE FUNCTION "count_family"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

declare
counts integer;

begin
   select count(*) into counts from family;
    return counts;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for count_people
-- ----------------------------
DROP FUNCTION IF EXISTS "count_people"();
CREATE OR REPLACE FUNCTION "count_people"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

declare
counts integer;

begin
   select count(*) into counts from people;
    return counts;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for count_relation
-- ----------------------------
DROP FUNCTION IF EXISTS "count_relation"();
CREATE OR REPLACE FUNCTION "count_relation"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

declare
counts integer;

begin
   select count(*) into counts from relation;
    return counts;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for delete_family
-- ----------------------------
DROP FUNCTION IF EXISTS "delete_family"();
CREATE OR REPLACE FUNCTION "delete_family"()
  RETURNS "pg_catalog"."trigger" AS $BODY$

begin
	delete from event where family_id = old.family_id;
	return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for delete_people
-- ----------------------------
DROP FUNCTION IF EXISTS "delete_people"();
CREATE OR REPLACE FUNCTION "delete_people"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
	delete from family where people_id = old.people_id;
	return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for family_delete_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "family_delete_trigger"();
CREATE OR REPLACE FUNCTION "family_delete_trigger"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
	delete from event where family_id = old.family_id;
	return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for family_delete_trigger_fun
-- ----------------------------
DROP FUNCTION IF EXISTS "family_delete_trigger_fun"();
CREATE OR REPLACE FUNCTION "family_delete_trigger_fun"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
    delete from gener where gener_id = old.family_id;
    return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for people_delete_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "people_delete_trigger"();
CREATE OR REPLACE FUNCTION "people_delete_trigger"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
	delete from family where people_id = old.people_id;
	return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for update_people
-- ----------------------------
DROP FUNCTION IF EXISTS "update_people"();
CREATE OR REPLACE FUNCTION "update_people"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
	begin 
		update family set people_id = new.people_id;
		return new;
	end;
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Triggers structure for table family
-- ----------------------------
CREATE TRIGGER "delete_trigger" AFTER DELETE ON "family"
FOR EACH ROW
EXECUTE PROCEDURE "public"."family_delete_trigger"();

-- ----------------------------
-- Primary Key structure for table family
-- ----------------------------
ALTER TABLE "family" ADD CONSTRAINT "family_pkey" PRIMARY KEY ("family_id");

-- ----------------------------
-- Triggers structure for table people
-- ----------------------------
CREATE TRIGGER "delete_peoples_trigger" AFTER DELETE ON "people"
FOR EACH ROW
EXECUTE PROCEDURE "public"."delete_people"();
CREATE TRIGGER "update_people_trigger" AFTER UPDATE OF "people_id" ON "people"
FOR EACH ROW
EXECUTE PROCEDURE "public"."update_people"();

-- ----------------------------
-- Primary Key structure for table people
-- ----------------------------
ALTER TABLE "people" ADD CONSTRAINT "people_pkey" PRIMARY KEY ("people_id");

-- ----------------------------
-- Primary Key structure for table staff
-- ----------------------------
ALTER TABLE "staff" ADD CONSTRAINT "staff_pkey" PRIMARY KEY ("staff_id");
